﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Data;

namespace AMStudio.Common
{
    public static class SimpleExcelReader
    {
        public static void LoadDataFromExcel(string filePath, string sheetName, System.Data.DataTable table)
        {
            OleDbConnection OleConn = null;
            try
            {
                string strConn;
                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=True;IMEX=2'";
                OleConn = new OleDbConnection(strConn);
                OleConn.Open();
                String sql = "SELECT * FROM  [" + sheetName + "$]";//可是更改Sheet名称，比如sheet2，等等    


                OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);
                OleDaExcel.Fill(table);

                OleConn.Close();
            }
            catch (Exception err)
            {
                throw new Exception("数据绑定Excel失败!失败原因：" + err.Message);
            }
            finally
            {
                if (OleConn != null)
                    OleConn.Close();
            }
        }
        //加载Excel    
        public static System.Data.DataTable LoadDataFromExcel(string filePath, string sheetName)
        {
            OleDbConnection OleConn = null;
            try
            {
                string strConn;
                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";
                OleConn = new OleDbConnection(strConn);
                OleConn.Open();
                String sql = "SELECT * FROM  [" + sheetName + "$]";//可是更改Sheet名称，比如sheet2，等等    
                //String sql = "SELECT * FROM  " + sheetName;//可是更改Sheet名称，比如sheet2，等等    
                //String sql = "SELECT * FROM [PLC_Ports$],[Load_UnLoad$]";//可是更改Sheet名称，比如sheet2，等等    

                OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);
                DataSet OleDsExcle = new DataSet();
                OleDaExcel.Fill(OleDsExcle, sheetName);

                OleConn.Close();
                return OleDsExcle.Tables[sheetName]; ;
            }
            catch (Exception err)
            {
                throw new Exception("数据绑定Excel失败!失败原因：" + err.Message);
            }
            finally
            {
                if (OleConn != null)
                    OleConn.Close();
            }
        }
    }
}
